package com.system.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.system.demo.entity.VO.QueryUsersVO;
import com.system.demo.entity.comment.UserAvatarDTO;
import com.system.demo.entity.database.User;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;


/**
 * 类描述: 对应数据库中表user的mapper
 * @since 1.0
 * @author luodeng
 */

@Mapper
public interface UserMapper extends BaseMapper<User> {
    @Select({
            "SELECT ",
            "    u.id AS id, ",
            "    u.uid AS uid, ",
            "    u.mail AS mail, ",
            "    u.avatar AS avatar, ",
            "    CASE WHEN sr.role_description LIKE '%管理员%' THEN a.admin_name ELSE s.student_name END AS userName, ",
            "    CASE WHEN sr.role_description LIKE '%管理员%' THEN a.admin_dep ELSE s.student_class END AS userDep, ",
            "    GROUP_CONCAT(sr.role_description SEPARATOR ', ') AS roleDescription ",
            "FROM ",
            "    `user` u ",
            "LEFT JOIN ",
            "    `student` s ",
            "ON ",
            "    u.uid = s.uid ",
            "LEFT JOIN ",
            "    `user_role` urole ",
            "ON ",
            "    u.uid = urole.uid ",
            "LEFT JOIN ",
            "    `system_role` sr ",
            "ON ",
            "    urole.role_id = sr.role_id ",
            "LEFT JOIN ",
            "    `admin` a ",
            "ON ",
            "    u.uid = a.uid AND sr.role_description LIKE '%管理员%'",
            "WHERE ",
            "    (#{role} IS NULL OR #{role} = '' OR (urole.role_id = #{role} AND urole.is_available = 1)) ",
            "    AND (#{uid} IS NULL OR #{uid} = '' OR u.uid = #{uid}) ",
            "    AND (#{stuClass} IS NULL OR #{stuClass} = '' OR s.student_class = #{stuClass}) ",
            "    AND (#{name} IS NULL OR #{name} = '' OR s.student_name LIKE CONCAT('%', #{name}, '%'))",
            "GROUP BY ",
            "    u.id, u.uid, u.username, u.mail, u.avatar, userName, userDep"
    })
    IPage<QueryUsersVO> getUsersByLimitsPage(
            IPage<QueryUsersVO> page,
            @Param("role") String role,
            @Param("uid") String uid,
            @Param("stuClass") String stuClass,
            @Param("name") String name
    );
    @Select("SELECT DISTINCT sr.role_name " +
            "FROM user_role ur " +
            "JOIN system_role sr ON ur.role_id = sr.role_id " +
            "WHERE ur.uid = #{uid} " +
            "AND ur.is_available = 1 " +
            "AND sr.is_available = 1")
    List<String> getAvailableRolesByUid(@Param("uid") String uid);

    @Select({
            "SELECT DISTINCT sm.value",
            "FROM user_role ur",
            "JOIN system_role sr ON ur.role_id = sr.role_id AND ur.is_available = 1 AND sr.is_available = 1",
            "JOIN system_role_menu srm ON sr.role_id = srm.role_id",
            "JOIN system_menu sm ON srm.menu_id = sm.id",
            "WHERE ur.uid = #{uid}"
    })
    List<String> getMenuValuesByUid(@Param("uid") String uid);

    @Select("SELECT DISTINCT mail FROM `user` WHERE mail = #{email}")
    String findEmailByMail(String email);



    @Select("SELECT sr.role_description " +
            "FROM user_role ur " +
            "JOIN system_role sr ON ur.role_id = sr.role_id " +
            "WHERE ur.uid = #{uid} AND ur.is_available = 1 AND sr.is_available = 1")
    List<String> getRoleDescriptionsByUid(String uid);

    @Select({
            "SELECT role_id",
            "FROM user_role",
            "WHERE uid = #{uid}",
            "AND is_available = 1"
    })
    List<Integer> getRoleIdsByUserId(@Param("uid") String uid);

    @Select({
            "SELECT avatar",
            "FROM user",
            "WHERE uid = #{uid}"
    })
    String findAvatarByUid(@Param("uid") String uid);

    @Select("<script>" +
            "SELECT DISTINCT uid, avatar " +
            "FROM user " +
            "WHERE uid IN " +
            "<foreach item='id' index='index' collection='ids' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    List<UserAvatarDTO> getUserAvatarsByIds(@Param("ids") List<String> userIds);


}